package com.eff.android.lib.helper;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import com.eff.android.api.model.Level;
import com.eff.android.api.model.Model;
import com.eff.android.api.model.ModelSubject;
import com.eff.android.api.model.Type;
import com.eff.android.config.EConstant;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/*
 * Database Helper Class
 */
public class EDatabaseHelper extends SQLiteOpenHelper {
 	
	private static EDatabaseHelper dbHelper = null;
	
	/*
     * Table Create Statements
     */
	
	// Word
    private static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS "
            + EConstant.TABLE + "(" + EConstant.KEY_ID_HINH + " TEXT PRIMARY KEY," + EConstant.KEY_VAN_BAN
            + " TEXT," + EConstant.KEY_Y_NGHIA + " TEXT," + EConstant.KEY_URI_HINH
            + " TEXT," + EConstant.KEY_PHIEN_AM_QUOC_TE + " TEXT," + EConstant.KEY_LOAI_HINH
            + " TEXT," + EConstant.KEY_LEVEL + " TEXT," + EConstant.KEY_MA_CHU_DE 
            + " TEXT," + EConstant.KEY_DATE_CREATE + " TEXT" + ")";
    
    // Subject
    private static final String CREATE_TABLE_SUBJECT = "CREATE TABLE IF NOT EXISTS "
            + EConstant.TABLE_SUBJECT + "(" + EConstant.KEY_ID_SUBJECT 
            + " TEXT PRIMARY KEY," + EConstant.KEY_NAME_SUBJECT + " TEXT" + ")";
    
    private EDatabaseHelper(Context context) {
        super(context, EConstant.DATABASE_NAME, null, EConstant.DATABASE_VERSION);
    }
    
    public static EDatabaseHelper getInstance(Context context){
    	
    	if (dbHelper == null)
    		dbHelper = new EDatabaseHelper(context); 	
    	
    	return dbHelper;
    }   

    /*
     * Creating a Table
     */
	@Override
    public void onCreate(SQLiteDatabase db) {
 
		Log.d("Tao bang word", CREATE_TABLE);
        // creating required tables
        db.execSQL(CREATE_TABLE);
        Log.d("Tao bang subject", CREATE_TABLE_SUBJECT);
        db.execSQL(CREATE_TABLE_SUBJECT);
    }
 
	/*
     * Upgrade Table
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + EConstant.TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + EConstant.TABLE_SUBJECT);
        
        // create new tables
        onCreate(db);
    }
    
    /*
     * Check Exist Model
     */
    public Boolean CheckExistModel(String model_id) {
                
    	SQLiteDatabase db = this.getReadableDatabase();
        
        String selectQuery = "SELECT  * FROM " + EConstant.TABLE + " WHERE "
                + EConstant.KEY_ID_HINH + " = " + model_id;
        
        Log.e(EConstant.LOG, selectQuery);
     
        Cursor c = db.rawQuery(selectQuery, null);
     
        if (c != null)
                if (c.moveToFirst())
                        return true;
        
        return false;
    }
    
    /*
     * Check Exist Subject
     */
    public Boolean CheckExistModelSubject(String subject_id) {
                
    	SQLiteDatabase db = this.getReadableDatabase();
        
        String selectQuery = "SELECT  * FROM " + EConstant.TABLE_SUBJECT + " WHERE "
                + EConstant.KEY_ID_SUBJECT + " = " + subject_id;
        
        Log.e(EConstant.LOG, selectQuery);
     
        Cursor c = db.rawQuery(selectQuery, null);
     
        if (c != null)
                if (c.moveToFirst())
                        return true;
        
        return false;
    }
    
    /*
     * Creating a model
     */
    public long createModel(Model model) {
        
        if (!CheckExistModel(model.getIdHinh()))
        {
        	SQLiteDatabase db = this.getWritableDatabase();
        	ContentValues values = CreateContentValuesFromModel(model);
             
            // insert row
            return db.insert(EConstant.TABLE, null, values);
             
        }
        
        return -1;
    }
    
    private ContentValues CreateContentValuesFromModel(Model model){
    	ContentValues values = new ContentValues();
    	if (model == null)
    		return values;
    	
        values.put(EConstant.KEY_ID_HINH, model.getIdHinh());
        values.put(EConstant.KEY_VAN_BAN, model.getVanBan());
        values.put(EConstant.KEY_Y_NGHIA, model.getYNghia());
        values.put(EConstant.KEY_URI_HINH, model.getUriHinh());
        values.put(EConstant.KEY_PHIEN_AM_QUOC_TE, model.getPhienAmQuocTe());
        values.put(EConstant.KEY_LOAI_HINH, model.getLoaiHinh());
        values.put(EConstant.KEY_LEVEL, model.getLevel());
        values.put(EConstant.KEY_MA_CHU_DE, model.getMaChuDe());
        values.put(EConstant.KEY_DATE_CREATE, model.getDateCreate());
        
        return values;
    }
    
    /*
     * Creating a model
     */
    public long createModelSubject(ModelSubject model) {
        
        if (!CheckExistModelSubject((model.getSubjectID())))
        {
        	SQLiteDatabase db = this.getWritableDatabase();
            
            ContentValues values = new ContentValues();
            values.put(EConstant.KEY_ID_SUBJECT, model.getSubjectID());
            values.put(EConstant.KEY_NAME_SUBJECT, model.getSubjectName());
            
            // insert row
            return db.insert(EConstant.TABLE_SUBJECT, null, values);
             
        }
        
        return -1;
    }
    
    /*
     * Get single model
     */
    public Model getModel(String model_id) {
        
    	Log.d("Ket qua tim : ", CheckExistModel(model_id).toString());
        
        if (CheckExistModel(model_id))
        {
                SQLiteDatabase db = this.getReadableDatabase();
             
                String selectQuery = "SELECT  * FROM " + EConstant.TABLE + " WHERE "
                        + EConstant.KEY_ID_HINH + " = " + model_id;
                
                Log.e(EConstant.LOG, selectQuery);
             
                Cursor c = db.rawQuery(selectQuery, null);
             
                if (c != null)
                    c.moveToFirst();
                return CreateModelFromCursor(c);
        }
        
        return new Model();
    }
    
    private Model CreateModelFromCursor(Cursor c){
    	Model md = new Model();
    	if (c == null)
    		return md;
    	
        md.setIdHinh(c.getString(c.getColumnIndex(EConstant.KEY_ID_HINH)));
        md.setVanBan(c.getString(c.getColumnIndex(EConstant.KEY_VAN_BAN)));
        md.setYNghia(c.getString(c.getColumnIndex(EConstant.KEY_Y_NGHIA)));
        md.setUriHinh(c.getString(c.getColumnIndex(EConstant.KEY_URI_HINH)));
        md.setPhienAmQuocTe(c.getString(c.getColumnIndex(EConstant.KEY_PHIEN_AM_QUOC_TE)));
        md.setLoaiHinh(c.getString(c.getColumnIndex(EConstant.KEY_LOAI_HINH)));
        md.setLevel(c.getString(c.getColumnIndex(EConstant.KEY_LEVEL)));
        md.setMaChuDe(c.getString(c.getColumnIndex(EConstant.KEY_MA_CHU_DE)));
        md.setDateCreate(c.getString(c.getColumnIndex(EConstant.KEY_DATE_CREATE)));
     
        return md;
    }
    
    /*
     * Get single model subject
     */
    public ModelSubject getModelSubject(String model_id) {
        
    	Log.d("Ket qua tim : ", CheckExistModelSubject(model_id).toString());
        
        if (CheckExistModelSubject(model_id))
        {
                SQLiteDatabase db = this.getReadableDatabase();
             
                String selectQuery = "SELECT  * FROM " + EConstant.TABLE_SUBJECT + " WHERE "
                        + EConstant.KEY_ID_SUBJECT + " = " + model_id;
                
                Log.e(EConstant.LOG, selectQuery);
             
                Cursor c = db.rawQuery(selectQuery, null);
             
                if (c != null)
                    c.moveToFirst();
             
                ModelSubject md = new ModelSubject();
                md.setSubjectID(c.getString(c.getColumnIndex(EConstant.KEY_ID_SUBJECT)));
                md.setSubjectName(c.getString(c.getColumnIndex(EConstant.KEY_NAME_SUBJECT)));
             
                return md;
        }
        
        return new ModelSubject();
    }
    
    /*
     *  Create and Select Query
     */
    private String setSelectQuery(int numberOfRecord, ModelSubject subject, Type.TypeType type, Level.LevelType level){    	
    	
    	Log.d("numberOfRecord", String.valueOf(numberOfRecord));
    	
    	Log.d("Subject ID", String.valueOf(subject.getSubjectID()));
    	
    	if (String.valueOf(subject.getSubjectID()).compareTo("-1") == 0)
    		Log.d("Subject Name", "Mixed");   		
    	else
    		Log.d("Subject Name", String.valueOf(subject.getSubjectName()));
    	
    	Log.d("type", type.toString());
    	Log.d("level", level.toString());
    	
    	int levelId = 0;
    	switch(level){
    		case Easy:
    			levelId = 1;
    			break;
    		case Normal:
    			levelId = 2;
    			break;
    		case Hard:
    			levelId = 3;
    			break;
    	}
    	Log.d("levelID", String.valueOf(levelId));
    	
    	String typeId = "tu";
    	switch(type){
		case Word:
			typeId = "tu";
			break;
		case Phrase:
			typeId = "cumtu";
			break;
    	}
    	Log.d("typeID", typeId.toString());
    	
    	if (String.valueOf(subject.getSubjectID()).compareTo("-1") == 0) {
    		return "SELECT  * FROM " + EConstant.TABLE + " WHERE " + EConstant.KEY_LOAI_HINH 
    				+ "='" + typeId + "' AND " + EConstant.KEY_LEVEL + "='" + levelId 
    				+ "' ORDER BY RANDOM()" + "ASC LIMIT " + numberOfRecord;
    	}
    	else {
    		return "SELECT  * FROM " + EConstant.TABLE + " WHERE " + EConstant.KEY_MA_CHU_DE + "=" 
    			+ subject.getSubjectID() + " AND " + EConstant.KEY_LOAI_HINH + "='" + typeId + "' AND " 
    			+ EConstant.KEY_LEVEL + "='" + levelId + "' ORDER BY RANDOM()" 
    			+ " ASC LIMIT " + numberOfRecord;
    	}  	
    }
    
    /*
     * getting all models
     * */
    public List<Model> getAllModels(int numberOfRecord, ModelSubject id, Type.TypeType type, Level.LevelType level) {
        
        List<Model> models = new ArrayList<Model>();
        String selectQuery = setSelectQuery(numberOfRecord, id, type, level);
     
        Log.e(EConstant.LOG, selectQuery);
     
        SQLiteDatabase db = this.getReadableDatabase();
        
	    Cursor c = db.rawQuery(selectQuery, null);
	     
	    if (c != null)
            {    
		    	// looping through all rows and adding to list
		        if (c.moveToFirst()) {
		            do {
		                Model md = CreateModelFromCursor(c);
		     
		             // adding to model list
		                models.add(md);
		            } while (c.moveToNext());
		        }
            }
	    
        Log.d("Get All Models:", String.valueOf(models.size()));
        return models;  	
        
    }
        /*
         * getting all models subject
         * */
        public List<ModelSubject> getAllModelsSubject() {
            
            List<ModelSubject> models = new ArrayList<ModelSubject>();
            String selectQuery = "SELECT  * FROM " + EConstant.TABLE_SUBJECT;
         
            Log.e(EConstant.LOG, selectQuery);
         
            SQLiteDatabase db = this.getReadableDatabase();
            
    	    Cursor c = db.rawQuery(selectQuery, null);
    	     
    	    if (c != null)
                {    
    		    	// looping through all rows and adding to list
    		        if (c.moveToFirst()) {
    		            do {
    		                ModelSubject md = new ModelSubject();
    		                md.setSubjectID(c.getString(c.getColumnIndex(EConstant.KEY_ID_SUBJECT)));
    		                md.setSubjectName(c.getString(c.getColumnIndex(EConstant.KEY_NAME_SUBJECT)));
    		                
    		             // adding to model subject list
    		                models.add(md);
    		            } while (c.moveToNext());
    		        }
                }
            Log.d("Get All Models Subject:", String.valueOf(models.size()));
            return models;  	
    }
    
    /*
     * getting all models under single EConstant.KEY_MA_CHU_DE
     * */
    public List<Model> getAllModelsByTag(String tag_name) {
        
        List<Model> listModels = new ArrayList<Model>();
     
        String selectQuery = "SELECT  * FROM " + EConstant.TABLE + " WHERE "
                + EConstant.KEY_MA_CHU_DE + " = " + tag_name;
     
        Log.e(EConstant.LOG, selectQuery);
     
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);
     
        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
            	Model md = CreateModelFromCursor(c);
     
             // adding to model list
                listModels.add(md);
            } while (c.moveToNext());
            
            return listModels;
        }
     
        List<Model> result = new ArrayList<Model>();
        return result;
    }
    
    /*
     * getting model count
     */
    public int getModelCount() {

        String countQuery = "SELECT  * FROM " + EConstant.TABLE;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
 
        int count = cursor.getCount();
        cursor.close();
 
        // return count
        return count;
    }
    
    /*
     * getting model subject count
     */
    public int getModelSubjectCount() {

        String countQuery = "SELECT  * FROM " + EConstant.TABLE_SUBJECT;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
 
        int count = cursor.getCount();
        cursor.close();
 
        // return count
        return count;
    }
    
    /*
     * Updating a model
     */
    public long updateModel(Model model) {
        
        if (!CheckExistModel(model.getIdHinh()))
        {
        	SQLiteDatabase db = this.getWritableDatabase();

        	ContentValues values = CreateContentValuesFromModel(model);
	        
	        // updating row
	        return db.update(EConstant.TABLE, values, EConstant.KEY_ID_HINH + " = ?",
	                new String[] { String.valueOf(model.getIdHinh()) });
        }
        
        return -1;
    }
    
    /*
     * Updating a model subject
     */
    public long updateModelSubject(ModelSubject model) {
        
        if (!CheckExistModelSubject(model.getSubjectID()))
        {
        	SQLiteDatabase db = this.getWritableDatabase();
             
	        ContentValues values = new ContentValues();
	        values.put(EConstant.KEY_ID_SUBJECT, model.getSubjectID());
	        values.put(EConstant.KEY_NAME_SUBJECT, model.getSubjectName());
	        
	        // updating row
	        return db.update(EConstant.TABLE_SUBJECT, values, EConstant.KEY_ID_SUBJECT + " = ?",
	                new String[] { String.valueOf(model.getSubjectID()) });
        }
        
        return -1;
    }
    
    /*
     * Deleting a model
     */
    public void deleteModel(String model_id) {
        
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(EConstant.TABLE, EConstant.KEY_ID_HINH + " = ?",
                        new String[] { String.valueOf(model_id) });
    }
    
    /*
     * Deleting a model subject
     */
    public void deleteModelSubject(String model_id) {
        
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(EConstant.TABLE_SUBJECT, EConstant.KEY_ID_SUBJECT + " = ?",
                        new String[] { String.valueOf(model_id) });
    }
    
    // closing database
    public void closeDB() {
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null && db.isOpen())
            db.close();
    }
    
    /**
     * get datetime
     * */
    public String getDateTime() {
        SimpleDateFormat dateFormat = new SimpleDateFormat(
                "yyyy-MM-dd HH:mm:ss", Locale.getDefault());
        Date date = new Date();
        return dateFormat.format(date);
    }
}